﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: May 12, 2016
-- Description:	Get next or previous TimeDiv based on a passed TimeDiv
-- =============================================
CREATE FUNCTION [dbo].[GetNextPrevTimeDiv]
(
	-- Add the parameters for the function here
	@TimeDiv nvarchar(10),
	@which nvarchar(10),
	@InstitutionID int = 0
)
RETURNS nvarchar(10)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @RetTimeDiv nvarchar(10)
	DECLARE @SEQUENCE int
	DECLARE @TimeDivType nvarchar(100)

		SELECT @SEQUENCE=Sequence, @TimeDivType=TimeDivType FROM
			(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, ShortName, TimeDivType
				FROM TimeDivisions) AS X
				 WHERE ShortName=@TimeDiv
		IF @which = 'NEXT'
			SET @SEQUENCE += 1
		ELSE
			SET @SEQUENCE -= 1
		SELECT @RetTimeDiv=ShortName FROM
				(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, ShortName, TimeDivType
				FROM TimeDivisions) AS X
				 WHERE Sequence=@SEQUENCE AND TimeDivType = @TimeDivType

	-- Return the result of the function
	RETURN @RetTimeDiv

END